import 'dart:io';
import 'package:path/path.dart';
import 'package:path_provider/path_provider.dart';
import 'package:sqflite_common_ffi/sqflite_ffi.dart';
import 'package:storage/model/bind_good_and_company_model_entity.dart';
import 'package:storage/model/user_name_entity.dart';
import 'package:storage/model/warehouse_in_out_model_entity.dart';

class DatabaseHelper {
  static const _databaseName = 'my_database3.db';

  static DatabaseHelper? _instance;

  DatabaseHelper._privateConstructor();

  factory DatabaseHelper() {
    _instance ??= DatabaseHelper._privateConstructor();
    return _instance!;
  }

  Database? _database;

  Future<Database> get database async {
    if (_database != null) return _database!;
    sqfliteFfiInit();
    var databaseFactory = databaseFactoryFfi;
    Directory documentsDirectory = await getApplicationDocumentsDirectory();
    String path = join(documentsDirectory.path, _databaseName);

    bool databaseExists = await File(path).exists();

    // if (!databaseExists) {
    // Make sure the directory exists
    await Directory(documentsDirectory.path).create(recursive: true);

    // If the database does not exist, create it
    _database = await databaseFactory.openDatabase(
      path,
    );
    if (!databaseExists) {
      await _onCreate(_database!);
    }
    return _database!;
  }

  Future<void> _onCreate(Database db) async {
    /// 创建商品表
    await db.execute('''
      CREATE TABLE storage_base (
        goodsId INTEGER PRIMARY KEY AUTOINCREMENT,
        goodsName TEXT NOT NULL,
        goodsSpecification TEXT,
        goodsType INTEGER,
        warehouseInTime TEXT,
        lastWarehouseOutTime TEXT,
        inventoryQuantity INTEGER NOT NULL,
        goodsPrice REAL,
        lastEditPrice REAL
      )
    ''');

    /// 创建公司表
    await db.execute('''
      CREATE TABLE company (
        companyId INTEGER PRIMARY KEY AUTOINCREMENT,
        companyName TEXT NOT NULL
      )
    ''');

    /// 创建公司绑定商品报价表
    await db.execute('''
      CREATE TABLE company_good_bind (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        companyId INTEGER NOT NULL,
        goodsId INTEGER NOT NULL,
        goodsPrice REAL,
        companyPrice REAL,
        companyName TEXT NOT NULL,
        goodsName TEXT NOT NULL,
        goodsSpecification TEXT
      )
    ''');

    /// 创建出入库表
    await db.execute('''
      CREATE TABLE warehouse_in_out (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        companyId INTEGER,
        count INTEGER,
        warehouseType INTEGER NOT NULL,
        goodsId INTEGER NOT NULL,
        goodsPrice REAL,
        companyPrice REAL,
        companyName TEXT,
        goodsName TEXT NOT NULL,
        goodsSpecification TEXT,
        warehouseTime TEXT
      )
    ''');
  }

  /// 新增报价信息
  Future<int> addOfferIndo(List<BindGoodAndCompanyModelEntity> rows) async {
    Database db = await database;
    int insertedCount = 0;

    Batch batch = db.batch();
    for (BindGoodAndCompanyModelEntity row in rows) {
      // 假设row.toJson()包含"id"字段时，表示该条记录需要更新；否则，需要插入
      if (row.id != null) {
        // 假设"id"字段代表主键
        // 更新操作
        batch.update(
          'company_good_bind',
          row.toJson(), // 数据对象转换为Map
          where: 'id = ?', // 基于id进行更新
          whereArgs: [row.id],
        );
      } else {
        // 插入操作，如果id为null，则认为是新记录
        batch.insert('company_good_bind', row.toJson());
      }
    }

    // 执行批处理操作
    List<dynamic> result = await batch.commit();
    insertedCount = result.length; // 返回的是受影响的行数列表，你可以累加或者直接取长度作为插入数量

    return insertedCount;
  }

  /// 查询报价信息
  Future<List<Map<String, dynamic>>> getOfferInfo({
    int? companyId,
    int? goodsId,
    String? companyName,
    String? goodsName,
    int? pageNum,
    int? pageSize,
  }) async {
    Database db = await database;
    String query =
        'SELECT * FROM company_good_bind WHERE 1=1'; // 初始化 WHERE 1=1 以便后面方便添加条件

    // 动态添加条件到查询语句
    if (companyId != null) {
      query += ' AND companyId = ?';
    }
    if (goodsId != null) {
      query += ' AND goodsId = ?';
    }
    if (companyName != null && companyName.isNotEmpty) {
      query += ' AND companyName LIKE ?'; // 使用LIKE进行模糊匹配，如果需要精确匹配则去掉%
    }
    if (goodsName != null && goodsName.isNotEmpty) {
      query += ' AND goodsName LIKE ?';
    }

    // 准备参数列表
    List<dynamic> params = [];
    if (companyId != null) params.add(companyId);
    if (goodsId != null) params.add(goodsId);
    if (companyName != null && companyName.isNotEmpty) {
      params.add('%$companyName%'); // 添加百分号进行模糊匹配
    }
    if (goodsName != null && goodsName.isNotEmpty) params.add('%$goodsName%');

    // 添加分页逻辑
    if (pageNum != null && pageSize != null) {
      int offset = (pageNum - 1) * pageSize;
      query += ' LIMIT ? OFFSET ?';
      params.add(pageSize);
      params.add(offset);
    }

    // 执行查询
    return await db.rawQuery(query, params);
  }

  /// 新增出入库记录
  Future<int> addWarehouseRecord(WarehouseInOutModelEntity row) async {
    Database db = await database;
    if (row.warehouseType == 0) {
      /// 此时是入库，改库存数量和入库时间
      await db.update('storage_base',
          {'warehouseInTime': row.warehouseTime}, // 只更新productName字段
          where: 'goodsId = ?',
          whereArgs: [row.goodsId]);
      List<Map<String, dynamic>> queryResult = await db.rawQuery(
          'SELECT * FROM storage_base WHERE 1=1 AND goodsId = ?',
          [row.goodsId]);
      int tempCount =
          (queryResult[0]['inventoryQuantity'] as int? ?? 0) + (row.count ?? 0);
      await db.update(
          'storage_base', {'inventoryQuantity': tempCount}, // 只更新productName字段
          where: 'goodsId = ?',
          whereArgs: [row.goodsId]);
    } else {
      /// 此时是出库，改库存数量和出库时间
      await db.update('storage_base',
          {'lastWarehouseOutTime': row.warehouseTime}, // 只更新productName字段
          where: 'goodsId = ?',
          whereArgs: [row.goodsId]);
      List<Map<String, dynamic>> queryResult = await db.rawQuery(
          'SELECT * FROM storage_base WHERE 1=1 AND goodsId = ?',
          [row.goodsId]);
      int tempCount =
          (queryResult[0]['inventoryQuantity'] as int? ?? 0) - (row.count ?? 0);
      await db.update(
          'storage_base', {'inventoryQuantity': tempCount}, // 只更新productName字段
          where: 'goodsId = ?',
          whereArgs: [row.goodsId]);
    }
    return await db.insert('warehouse_in_out', row.toJson());
  }

  /// 获取出入库记录列表
  Future<CommonListModel> getWarehouseRecordList(
      int warehouseType, {
        String? goodName,
        String? companyName,
        String? warehouseTime, // 新增时间查询参数 (yyyy-MM-dd)
        int? pageNum,
        int? pageSize,
      }) async {
    Database db = await database;

    // 初始化查询语句（1=1 使拼接更方便）
    String queryBase = 'SELECT * FROM warehouse_in_out WHERE 1=1';
    String countQuery = 'SELECT COUNT(*) AS total FROM warehouse_in_out WHERE 1=1';

    // 存储查询参数
    List<dynamic> conditions = [];
    List<dynamic> countConditions = [];

    // 仓库类型（必填参数）
    queryBase += ' AND warehouseType = ?';
    countQuery += ' AND warehouseType = ?';
    conditions.add(warehouseType);
    countConditions.add(warehouseType);

    // 商品名称模糊查询
    if (goodName != null && goodName.isNotEmpty) {
      queryBase += ' AND goodsName LIKE ?';
      countQuery += ' AND goodsName LIKE ?';
      conditions.add('%$goodName%');
      countConditions.add('%$goodName%');
    }

    // 公司名称模糊查询
    if (companyName != null && companyName.isNotEmpty) {
      queryBase += ' AND companyName LIKE ?';
      countQuery += ' AND companyName LIKE ?';
      conditions.add('%$companyName%');
      countConditions.add('%$companyName%');
    }

    // **新增时间查询（warehouseTime）**
    if (warehouseTime != null && warehouseTime.isNotEmpty) {
      queryBase += ' AND warehouseTime LIKE ?';
      countQuery += ' AND warehouseTime LIKE ?';
      conditions.add('$warehouseTime%'); // 模糊匹配当天的所有时间
      countConditions.add('$warehouseTime%');
    }

    // **添加分页逻辑**
    if (pageNum != null && pageSize != null) {
      int offset = (pageNum - 1) * pageSize;
      queryBase += ' LIMIT ? OFFSET ?';
      conditions.add(pageSize);
      conditions.add(offset);
    }

    // **执行主查询**
    List<Map<String, dynamic>> data = await db.rawQuery(queryBase, conditions);

    // **执行总记录数查询**
    final countResult = await db.rawQuery(countQuery, countConditions);
    int total = countResult.first['total'] as int;

    // **返回通用类**
    return CommonListModel(data: data, total: total);
  }


  /// 新增商品
  Future<int> addGoods(UserNameEntity row) async {
    Database db = await database;
    return await db.insert('storage_base', row.toJson());
  }

  /// 编辑商品
  Future<int> editGood(UserNameEntity row) async {
    Database db = await database;
    int id = row.goodsId!;
    return await db.update('storage_base', row.toJson(),
        where: 'goodsId = $id');
  }

  Future<int> updateProductNameById(int goodsId, String newProductName) async {
    Database db = await database;
    // 注意这里的'productName = ?'和下面的[newProductName]是对应的
    // 这里的'$id'是字符串模板插值，用于放置goodsId变量的值
    return await db.update(
        'storage_base', {'productName': newProductName}, // 只更新productName字段
        where: 'goodsId = ?',
        whereArgs: [goodsId]);
  }

  /// 获取商品列表
  Future<CommonListModel> getGoodsList({
    String? goodName,
    String? warehouseInDate,  // 新增入库时间查询参数 (yyyy-MM-dd)
    String? warehouseOutDate, // 新增出库时间查询参数 (yyyy-MM-dd)
    int? pageNum,
    int? pageSize,
  }) async {
    Database db = await database;

    // 基础查询语句
    String query = 'SELECT * FROM storage_base';
    String countQuery = 'SELECT COUNT(*) AS total FROM storage_base';
    List<dynamic> whereArgs = [];
    List<dynamic> countWhereArgs = [];
    List<String> conditions = []; // 用于存储 WHERE 子句的条件

    // 处理商品名称查询
    if (goodName != null && goodName.isNotEmpty) {
      conditions.add('goodsName LIKE ?');
      whereArgs.add('%$goodName%');
      countWhereArgs.add('%$goodName%');
    }

    // 处理入库时间查询
    if (warehouseInDate != null && warehouseInDate.isNotEmpty) {
      conditions.add('warehouseInTime LIKE ?');
      whereArgs.add('$warehouseInDate%');
      countWhereArgs.add('$warehouseInDate%');
    }

    // 处理出库时间查询
    if (warehouseOutDate != null && warehouseOutDate.isNotEmpty) {
      conditions.add('warehouseOutTime LIKE ?');
      whereArgs.add('$warehouseOutDate%');
      countWhereArgs.add('$warehouseOutDate%');
    }

    // 拼接 WHERE 语句
    if (conditions.isNotEmpty) {
      String whereClause = ' WHERE ' + conditions.join(' AND ');
      query += whereClause;
      countQuery += whereClause;
    }

    // 添加分页逻辑
    if (pageNum != null && pageSize != null) {
      int offset = (pageNum - 1) * pageSize;
      query += ' LIMIT ? OFFSET ?';
      whereArgs.add(pageSize);
      whereArgs.add(offset);
    }

    // 执行查询
    List<Map<String, dynamic>> data = await db.rawQuery(query, whereArgs);

    // 执行总记录数查询
    final countResult = await db.rawQuery(countQuery, countWhereArgs);
    int total = countResult.first['total'] as int;

    // 返回通用类
    return CommonListModel(data: data, total: total);
  }


  /// 删除商品
  Future<int> deleteGood(List<int> idList) async {
    if (idList.isEmpty) {
      // 如果没有ID需要删除，可以直接返回0
      return 0;
    }
    Database db = await database;
    // 将ID列表转换为字符串，每个ID用逗号分隔，并放在括号内
    String ids = idList.join(',');

    // 删除信息表
    String sql = 'DELETE FROM storage_base WHERE goodsId IN ($ids)';
    // 删除绑定表
    String sql2 = 'DELETE FROM company_good_bind WHERE goodsId IN ($ids)';
    // 执行删除操作
    await db.rawDelete(sql);
    await db.rawDelete(sql2);

    return 1;
  }

  /// 新增公司
  Future<void> addCompany(List<String> companyNames) async {
    Database db = await database;
    if (companyNames.isNotEmpty) {
      final Batch batch = db.batch();

      // 对每个公司名称执行插入操作
      for (String companyName in companyNames) {
        batch.insert('company', {'companyName': companyName},
            conflictAlgorithm: ConflictAlgorithm.replace);
      }

      // 执行批量操作
      await batch.commit();
    }
  }
  /// 获取公司列表
  Future<CommonListModel> getCompanyList({
    String? companyName,
    int? pageNum,
    int? pageSize,
  }) async {
    Database db = await database;

    // 构建查询条件
    String query = 'SELECT * FROM company';
    String countQuery = 'SELECT COUNT(*) AS total FROM company';
    List<dynamic> whereArgs = [];
    List<dynamic> countWhereArgs = [];

    if (companyName != null && companyName.isNotEmpty) {
      query += ' WHERE companyName LIKE ?';
      countQuery += ' WHERE companyName LIKE ?';
      whereArgs.add('%$companyName%');
      countWhereArgs.add('%$companyName%');
    }

    // 添加分页逻辑
    if (pageNum != null && pageSize != null) {
      int offset = (pageNum - 1) * pageSize;
      query += ' LIMIT ? OFFSET ?';
      whereArgs.add(pageSize);
      whereArgs.add(offset);
    }

    // 执行查询
    List<Map<String, dynamic>> data = await db.rawQuery(query, whereArgs);

    // 执行总记录数查询
    final countResult = await db.rawQuery(countQuery, countWhereArgs);
    int total = countResult.first['total'] as int;

    return CommonListModel(data: data, total: total);
  }


  /// 删除公司
  Future<int> deleteCompany(List<int> idList) async {
    if (idList.isEmpty) {
      // 如果没有ID需要删除，可以直接返回0
      return 0;
    }
    Database db = await database;
    // 将ID列表转换为字符串，每个ID用逗号分隔，并放在括号内
    String ids = idList.join(',');

    // 使用IN子句来匹配多个ID
    String sql = 'DELETE FROM company WHERE companyId IN ($ids)';
    // 删除绑定表
    String sql2 = 'DELETE FROM company_good_bind WHERE companyId IN ($ids)';
    // 执行删除操作
    await db.rawDelete(sql);
    await db.rawDelete(sql2);
    return 1;
  }
}

class CommonListModel {
  final List<Map<String, dynamic>> data;
  final int total;

  CommonListModel({
    required this.data,
    required this.total,
  });
}
